/************************************************************************************/
/*  Program name CAPGLIMP_AUTOPOST_MAPPING.sql                                      */
/*  Creater:  CHAN BILLY    (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 29 MAR, 2006   CHAN BILLY            First created                               */
/************************************************************************************/

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE

	lv_Prog_ID		VARCHAR2(100) 	:=	'CAPGLIMP_AUTOPOST_MAPPING';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_Old_ID	NUMBER;
	lv_New_ID	NUMBER;
	lv_JE_Source	VARCHAR2(100);

BEGIN
	
		SELECT  B.AUTOPOST_SET_ID,
				(	SELECT  C.AUTOPOST_SET_ID
			   		FROM GL_AUTOMATIC_POSTING_SETS C
					WHERE C.SET_OF_BOOKS_ID=CAPBRIMP_MAPPING.GetSOBIDMapping(B.SET_OF_BOOKS_ID)),
				A.JE_SOURCE_NAME
		INTO
				lv_Old_ID,
				lv_New_ID,
				lv_JE_Source	  
		FROM
			CRST_GL_AUTO_POSTING_OPTS A,
			GL_AUTOMATIC_POSTING_SETS B
		WHERE
			A.AUTOPOST_SET_ID = B.AUTOPOST_SET_ID
		AND
			A.JE_SOURCE_NAME = 'Payables';
		
		INSERT	INTO	CRST_GL_AUTOPOSTING_MAP
		VALUES
		(
		lv_Old_ID,
		lv_New_ID,
		lv_JE_Source
		);

	   SELECT  B.AUTOPOST_SET_ID,
				(	SELECT  C.AUTOPOST_SET_ID
			   		FROM GL_AUTOMATIC_POSTING_SETS C
					WHERE C.SET_OF_BOOKS_ID=CAPBRIMP_MAPPING.GetSOBIDMapping(B.SET_OF_BOOKS_ID)),
				A.JE_SOURCE_NAME
		INTO
				lv_Old_ID,
				lv_New_ID,
				lv_JE_Source	  
		FROM
			CRST_GL_AUTO_POSTING_OPTS A,
			GL_AUTOMATIC_POSTING_SETS B
		WHERE
			A.AUTOPOST_SET_ID = B.AUTOPOST_SET_ID
		AND
			A.JE_SOURCE_NAME = 'Purchasing';
		
		INSERT	INTO	CRST_GL_AUTOPOSTING_MAP
		VALUES
		(
		lv_Old_ID,
		lv_New_ID,
		lv_JE_Source
		);

		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID,
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);

	COMMIT;
END;
/